package com.itravel.cms.services.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Service;

import com.itravel.cms.dao.ScenicSpotDao;
import com.itravel.cms.domain.ScenicSpot;
import com.itravel.cms.domain.ScenicSpotKeyword;
import com.itravel.cms.services.ExcelDataService;
import com.itravel.cms.utils.POIUtil;
import com.itravel.cms.utils.ScenicSpotExcelColumnNames;
import com.itravel.cms.utils.ScenicSpotKeywordExcelColumnNames;

@Service
public class ExcelDataServiceImpl implements ExcelDataService {
	private Logger logger = Logger.getLogger(ExcelDataServiceImpl.class);;

	private ScenicSpotDao scenicSpotDao;
	
	public ScenicSpotDao getScenicSpotDao() {
   	return scenicSpotDao;
   }

	public void setScenicSpotDao(ScenicSpotDao scenicSpotDao) {
   	this.scenicSpotDao = scenicSpotDao;
   }

	public void importDataWithFilePath(String excelFilePath) throws IOException {
		File excelFile = new File(excelFilePath);
		InputStream inputStream = new FileInputStream(excelFile);
		HSSFWorkbook excelWorkBook = new HSSFWorkbook(inputStream);
		this.importScenicSpotData(excelWorkBook);
	//				this.importKeywordsData(excelWorkBook);
	}
	
	/**************************************************************
	 * 解析"景点"excel数据并导入数据库
	 **************************************************************/
	private boolean importScenicSpotData(HSSFWorkbook excelWorkBook) {
		HSSFSheet excelSheet = excelWorkBook.getSheet("景点");
		logger.info("There are " + excelSheet.getLastRowNum() + " rows scenicspot data.");
		List<ScenicSpot> scenicSpots = new ArrayList<ScenicSpot>();
		for (int rowIndex = 1; rowIndex < excelSheet.getLastRowNum(); rowIndex++) {
			Row row = excelSheet.getRow(rowIndex);
			ScenicSpot scenicSpot = this.parseScenicSpotFromExcelSheetRow(row);
			scenicSpots.add(scenicSpot);
		}
		return this.saveScenicSpotsToDB(scenicSpots);
	}

	private ScenicSpot parseScenicSpotFromExcelSheetRow(Row row) {
		ScenicSpot scenicSpot = new ScenicSpot();
		String serialNumber = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.SERIAL_NUMBER.columnIndex());
		String name = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.NAME.columnIndex());
		String namePinyin = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.NAME_PINYIN.columnIndex());
		String provinceSerialNumber = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.PROVINCE_SERIAL_NUMBER.columnIndex());
		String citySerialNumber = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.CITY_SERIAL_NUMBER.columnIndex());
		String description = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.DESCRIPTION.columnIndex());
		String address = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.ADDRESS.columnIndex());
		String selfRoute = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.SELF_ROUTE.columnIndex());
		String driveRoute = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.DRIVE_ROUTE.columnIndex());
		String adultsFee = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.ADULTS_FEE.columnIndex());
		String childrenFee = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.CHILDREN_FEE.columnIndex());
		String studentsFee = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.STUDENTS_FEE.columnIndex());
		String seniorsFee = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.SENIORS_FEE.columnIndex());
		String disablesFee = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.DISABLES_FEE.columnIndex());
		String soldiersFee = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.SOLDIERS_FEE.columnIndex());
		String otherFee = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.OTHER_FEE.columnIndex()); 
		String workingDateTime = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.WORKING_DATETIME.columnIndex());
		String phoneNumbers = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.PHONE_NUMBERS.columnIndex());
		String note = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.NOTE.columnIndex());
		String gpsLongitude = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.GPS_LONGITUDE.columnIndex());
		String gpsLatitude = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.GPS_LATITUDE.columnIndex());
		String coverImgPath = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.COVER_IMG_PATH.columnIndex());
		String guidMapImgPath = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.GUID_MAP_IMG_PATH.columnIndex());
		String recommendedRank = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.RECOMMENDED_RANK.columnIndex());
		String parentScenicSpotSerialNumber = POIUtil.getStringCellValue(row, ScenicSpotExcelColumnNames.PARENT_SCENIC_SPOT_SERIAL_NUMBER.columnIndex());

		scenicSpot.setSerialNumber(serialNumber);
		scenicSpot.setName(name);
		scenicSpot.setNamePinyin(namePinyin);
		scenicSpot.setProvinceSerialNumber(provinceSerialNumber);
		scenicSpot.setCitySerialNumber(citySerialNumber);
		scenicSpot.setDescription(description);
		scenicSpot.setAddress(address);
		scenicSpot.setSelfRoute(selfRoute);
		scenicSpot.setDriveRoute(driveRoute);
		scenicSpot.setAdultsFee(adultsFee);
		scenicSpot.setChildrenFee(childrenFee);
		scenicSpot.setStudentsFee(studentsFee);
		scenicSpot.setSeniorsFee(seniorsFee);
		scenicSpot.setDisablesFee(disablesFee);
		scenicSpot.setSoldiersFee(soldiersFee);
		scenicSpot.setOtherFee(otherFee);
		scenicSpot.setWorkingDateTime(workingDateTime);
		scenicSpot.setPhoneNumbers(phoneNumbers);
		scenicSpot.setNote(note);
		scenicSpot.setGpsLongitude(gpsLongitude);
		scenicSpot.setGpsLatitude(gpsLatitude);
		scenicSpot.setCoverImgPath(coverImgPath);
		scenicSpot.setGuidMapImgPath(guidMapImgPath);
		scenicSpot.setRecommendedRank(recommendedRank);
		scenicSpot.setParentScenicSpotSerialNumber(parentScenicSpotSerialNumber);
		return scenicSpot;
	}
	
	private boolean saveScenicSpotsToDB(List<ScenicSpot> scenicSpots) {
		for (ScenicSpot scenicSpot : scenicSpots) {
			try {
				scenicSpotDao.insert(scenicSpot);
			} catch (Exception e) {
				logger.error(e.getMessage(), e);
				return false;
			} 
		}
		return true;
	}

	/**************************************************************
	 * 解析"关键词"excel数据并导入数据库
	 **************************************************************/
	
	private boolean importKeywordsData(HSSFWorkbook excelWorkBook) {
		HSSFSheet excelSheet = excelWorkBook.getSheet("关键词");
		logger.info("There are " + excelSheet.getLastRowNum() + " rows keywords data.");
		List<ScenicSpotKeyword> scenicSpotKeywords = new ArrayList<ScenicSpotKeyword>();
		for (int rowIndex = 1; rowIndex < excelSheet.getLastRowNum(); rowIndex++) {
			Row row = excelSheet.getRow(rowIndex);
			ScenicSpotKeyword scenicSpotsKeyword = this.parseScenicSpotKeywordFromExcelSheetRow(row);
			scenicSpotKeywords.add(scenicSpotsKeyword);
		}
		return this.saveScenicSpotKeywordsToDB(scenicSpotKeywords);		
	}

	private ScenicSpotKeyword parseScenicSpotKeywordFromExcelSheetRow(Row row) {
		ScenicSpotKeyword scenicSpotKeyword = new ScenicSpotKeyword();
		String serialNumber = POIUtil.getStringCellValue(row, ScenicSpotKeywordExcelColumnNames.SERIAL_NUMBER.columnIndex());
		String name = POIUtil.getStringCellValue(row, ScenicSpotKeywordExcelColumnNames.NAME.columnIndex());
		String pinyin = POIUtil.getStringCellValue(row, ScenicSpotKeywordExcelColumnNames.PINYIN.columnIndex());
		
		scenicSpotKeyword.setSerialNumber(serialNumber);
		scenicSpotKeyword.setName(name);
		scenicSpotKeyword.setPinyin(pinyin);
		return scenicSpotKeyword;
	}
	
	private boolean saveScenicSpotKeywordsToDB(List<ScenicSpotKeyword> scenicSpotKeywords) {
		return true;
	}
	
}